import json
import pandas as pd
import time
from gavial_scriptsdk.JdbcUtil import *


def load_variable(variable_name):
  """
  load variable from db
  :param variable_name: variable namee
  :return: variable value
  """
  try:
    sql = "select * from %s.%s where name='%s'" % \
          (CURRENT_SCHEMA, VARIABLE_TABLE_NAME, variable_name)
    conn = get_jdbc_connection()
    curs = conn.cursor()
    curs.execute(sql)
    data = curs.fetchone()
    if data is not None:
      variable_type = data[1]
      value = data[2].getSubString(1, data[2].length())
      if variable_type == 'LONG':
        return int(value)
      elif variable_type == 'DOUBLE':
        return float(value)
      elif variable_type == 'BOOLEAN':
        return bool(value)
      elif variable_type == 'TIMESTAMP':
        return time.strptime(value, TIME_FORMAT)
      elif variable_type == 'MAP' or variable_type == 'LIST':
        return json.loads(value)
      else:
        return value
  except Exception as e:
    raise
  finally:
    curs.close()
    conn.close()
  return None


def export_variable(value, variable_name):
  """
  export variable to db
  :param value: variable value
  :param variable_name: variable name
  :return: None
  """
  try:
    conn = get_jdbc_connection()
    curs = conn.cursor()
    variable_value = str(value)
    if isinstance(value, int):
      variable_type = 'LONG'
    elif isinstance(value, float):
      variable_type = 'DOUBLE'
    elif isinstance(value, bool):
      variable_type = 'BOOLEAN'
    elif isinstance(value, time.struct_time):
      variable_type = 'TIMESTAMP'
    elif isinstance(value, dict):
      variable_type = 'MAP'
      variable_value = json.dumps(value)
    elif isinstance(value, list):
      variable_value = json.dumps(value)
      variable_type = 'LIST'
    else:
      variable_type = 'STRING'
    sql = "insert into %s.%s(name,type,value) values('%s', '%s', '%s')" % (
      CURRENT_SCHEMA, VARIABLE_TABLE_NAME, variable_name, variable_type,
      variable_value)
    curs.execute(sql)
  except Exception as de:
    raise
  finally:
    curs.close()
    conn.close()


def load_table(table):
  """
  Reads jdbc table data and returns a Pandas DataFrame

  uses jaydebeapi.connect and doc strings :-)
  https://pypi.python.org/pypi/JayDeBeApi/

  :param table: select table
  :return: Pandas DataFrame
  """

  try:
    sql = "select * from %s.%s" % (CURRENT_SCHEMA, table)
    conn = get_jdbc_connection()
    curs = conn.cursor()
    curs.execute(sql)
    # getting column headers
    columns = [desc[0] for desc in curs.description]
    cloumn_types = [desc[1] for desc in curs.description]
    # convert the list of tuples from fetchall() to a df
    new_rows = []
    for row in curs.fetchall():
      new_row = []
      for i in range(0, len(cloumn_types)):
        if cloumn_types[i] == jaydebeapi.TEXT and row[i] is not None:
          new_row.append(row[i].getSubString(1, row[i].length()))
        else:
          new_row.append(row[i])
      new_rows.append(new_row)
    return pd.DataFrame(new_rows, columns=columns)
  except Exception as de:
    raise


def save_to_table(df, table):
  """
  save Pandas DataFrame to db by jdbc

  :param df: Pandas DataFrame.
  :param table: table name that the Pandas DataFrame would be saved to.
  :return: None
  """
  try:
    conn = get_jdbc_connection()
    types = dtypes_to_str(df.dtypes)
    create_table(conn, types, table)
    curs = conn.cursor()
    for index, row in df.iterrows():
      insert_sql = "insert into %s.%s(" % (CURRENT_SCHEMA, table)
      values_sql = " values("
      for (column, data_type) in types.items():
        insert_sql = "%s%s," % (insert_sql, column)
        if data_type == "INTEGER" or data_type == "BIGINT" \
            or data_type == "FLOAT" or data_type == "DOUBLE" \
            or data_type == "BOOLEAN":
          values_sql = "%s%s," % (values_sql, row[column])
        else:
          if isinstance(row[column], time.struct_time):
            value = time.strftime(TIME_FORMAT, row[column])
          else:
            value = str(row[column])
          values_sql = "%s'%s'," % (values_sql, value)
      insert_sql = "%s)%s)" % (
      insert_sql[0:len(insert_sql) - 1], values_sql[0:len(values_sql) - 1])
      curs.execute(insert_sql)
    sql = "insert into %s.%s(name,type,value) values('%s', '%s', '%s')" % (
      CURRENT_SCHEMA, VARIABLE_TABLE_NAME, table, "TABLE", table)
    curs.execute(sql)
  except Exception as de:
    raise
  finally:
    curs.close()
    conn.close()


def pivot_df(df=None, index=None, columns=None, values=None):
  """
  pivot DataFrame and returns a new Pandas DataFrame after pivot

  :param df: DataFrame
  :param index: index column name
  :param columns: column for columns name
  :param values: column for values
  :return: Pandas DataFrame
  """
  if pd is None or index is None or columns is None or values is None:
    raise Exception("df, index, columns, values can not be None!")
  temp_df = df.pivot(index=index, columns=columns, values=values)
  values = temp_df.values.tolist()
  indexes = [index for index in temp_df.index]
  for i in range(0, len(indexes)):
    values[i].insert(0, indexes[i])
  columns = temp_df.columns.tolist()
  columns.insert(0, index)
  new_df = pd.DataFrame(values, columns=columns)
  return new_df


def create_table(conn, dtypes, table):
  try:
    drop_table = "DROP TABLE IF EXISTS %s.%s CASCADE" % (CURRENT_SCHEMA, table)
    curs = conn.cursor()
    # drop table
    curs.execute(drop_table)
    create_table_sql = "CREATE TABLE %s.%s(" % (CURRENT_SCHEMA, table)
    for (column, data_type) in dtypes.items():
      create_table_sql = "%s%s %s," % (create_table_sql, column, data_type)
    create_table_sql = create_table_sql[0:len(create_table_sql) - 1] + ")"
    # create table
    curs.execute(create_table_sql)
  except Exception as e:
    raise
  finally:
    curs.close()


def dtypes_to_str(dtypes):
  types = {}
  for (column, dtype) in dtypes.items():
    if dtype == 'float16' or dtype == 'float32':
      types[column] = 'FLOAT'
    elif dtype == 'float64':
      types[column] = 'DOUBLE'
    elif dtype == 'int8' or dtype == 'uint8' or dtype == 'int16' \
        or dtype == 'uint16' or dtype == 'int32' or dtype == 'uint32':
      types[column] = 'INTEGER'
    elif dtype == 'int64' or dtype == 'uint64':
      types[column] = 'BIGINT'
    elif 'bool' in str(dtype):
      types[column] = 'BOOLEAN'
    elif 'datetime64' in str(dtype):
      types[column] = 'TIMESTAMP'
    else:
      types[column] = 'CLOB'
  return types


json_str = load_variable('JSON_STRING')

if json_str is not None:
  response_data = json.loads(json_str, encoding='utf-8')
